﻿Imports System.IO
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.UI.DataVisualization.Charting

Partial Class Reportes_RPTCAPAgrupado
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            'cargar proyectos
            llenarCombos()
        End If


    End Sub


    Protected Sub btnConsultar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConsultar.Click
        cargardatos()
        generarGrafica()
    End Sub
    Sub cargardatos()
        'Using bd As New bdReportesDataContext
        '    grdResultado.DataSource = bd.usp_RPTCAPAgrupado(ddlProyectos.SelectedValue, "titulo")
        '    grdResultado.DataBind()
        'End Using
        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()
            Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTCAPAgrupado", cnn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@grupo", ddlProyectos.SelectedValue)
                cmd.Parameters.AddWithValue("@orden", ddlOrden.SelectedValue)
                grdResultado.DataSource = cmd.ExecuteReader
                grdResultado.DataBind()
                Dim titulo As String = "<h2> Reporte por " & ddlProyectos.SelectedItem.Text & "<br/>Fecha:" & Date.Now.ToShortDateString & "<h2>"
                lblTitulo.Text = titulo
            End Using


            cnn.Close()

        End Using
    End Sub
    Protected Sub btnExpt_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'Handles btnExpt.Click

    End Sub

    Protected Sub exportarAExcel()


        Dim sb As StringBuilder = New StringBuilder()
        Dim sw As StringWriter = New StringWriter(sb)
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim pagina As Page = New Page

        Dim form = New HtmlForm
        pagina.EnableEventValidation = False
        pagina.DesignerInitialize()
        pagina.Controls.Add(form)

        'form.Controls.Add(tabla)
        form.Controls.Add(Me.grdResultado)
        pagina.RenderControl(htw)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment;filename=Reporte.xls")
        Response.Charset = "UTF-8"
        Response.ContentEncoding = Encoding.Default
        Response.Write(sb.ToString())
        Response.End()

    End Sub

    Protected Sub btnExp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExp.Click
        cargardatos()
        exportarAExcel()
    End Sub

    Sub llenarCombos()
        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()

            LlenarItems(ddlProyectos, "RPTCAPAgrupado", cnn)

            cnn.Close()

        End Using
    End Sub

    Sub LlenarItems(ByVal objeto As Object, ByVal pregunta As String, ByRef cnn As SqlConnection)
        If pregunta = "sino" Then : objeto.Items.Add(New ListItem("Sí", 1)) : objeto.Items.Add(New ListItem("No", 2)) : Exit Sub : End If
        Using drDatos As SqlDataReader = Funciones.TraerListas(pregunta, cnn)
            With objeto
                .DataSource = drDatos
                .DataTextField = "nombreopcion"
                .DataValueField = "valoropcion"
                .DataBind()
            End With
        End Using
    End Sub
    Sub generarGrafica()

        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()
            Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTCAPAgrupado", cnn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@grupo", ddlProyectos.SelectedValue)
                cmd.Parameters.AddWithValue("@orden", ddlOrden.SelectedValue)


                Chart1.DataSource = cmd.ExecuteReader
                Chart1.ImageType = DataVisualization.Charting.ChartImageType.Png
                Chart1.Series("Default").XValueMember = "Detalle"
                Chart1.Series("Default").YValueMembers = "Cantidad"
                Chart1.DataBind()

                With Chart1

                    .Series("Default").ChartType = SeriesChartType.Column
                    .Series("Default").LabelFormat = "N0"
                    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                    '.ChartAreas("ChartArea1").AxisY.Maximum = 1

                    ' Set series point width
                    .Series("Default")("PointWidth") = "0.6"

                    ' Show data points labels
                    .Series("Default").IsValueShownAsLabel = False

                    ' Set data points label style
                    .Series("Default")("BarLabelStyle") = "Center"

                    ' Show as 3D
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True

                    ' Draw as 3D Cylinder
                    .Series("Default")("DrawingStyle") = "Cylinder"



                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False

                    'Dim titulo As String = "<h2> Reporte por " & ddlProyectos.SelectedItem.Text & "<br/>Fecha:" & Date.Now.ToShortDateString & "<h2>"

                    .Titles("Title1").Text = " Reporte por " & ddlProyectos.SelectedItem.Text

                End With


            End Using


            cnn.Close()

        End Using




    End Sub
End Class
